package com.lv.LvSpecialRecommendation.dao;

import com.aqgj.util.BaseDaoImpl;
import com.lv.LvSpecialRecommendation.bean.LvSpecialRecommendation;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Repository("LvSpecialRecommendationDao")
public class LvSpecialRecommendationDao extends BaseDaoImpl<LvSpecialRecommendation> {

    public String save(LvSpecialRecommendation lvSpecialRecommendation){
        this.getHibernateTemplate().save(lvSpecialRecommendation);
        return lvSpecialRecommendation.getId();
    }

    //搜索线路站点信息（新增特价推荐）
    public List<Map> searchLvTourismForSale(String startSite, String endSite) {
        String sql= "select r.id id,s.start_site_name startArea,s.terminal_site_name endArea,z.full_name fullName,r.min_price minPrice,z.id cid,r.tourism_name from lv_tourism_for_sale r " +
                    "inner join ztc_transport z on z.id=r.fk_ztc_transport_id " +
                    "inner join lv_tourism_model s on s.id =r.fk_tourism_model_id and s.start_site_name like '%"+startSite+"%' and s.terminal_site_name like '%"+endSite+"%' " +
                    "where r.`status`=1 group by r.id";
        return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }
    //获取所有正常推荐
    public List<Map> getSpecialALLList(Integer type) {
        String sql= "SELECT r.start_site_name startArea,r.terminal_site_name endArea,c.min_price minPrice,s.`type`,s.id id,c.server_date serverDate,z.id cid,z.full_name fullName " +
                    "FROM lv_special_recommendation s " +
                    "INNER JOIN lv_tourism_model r ON r.id=s.fk_tourism_model_id " +
                    "INNER JOIN ztc_transport z ON z.id=r.fk_transport_id " +
                    "INNER JOIN lv_tourism_for_sale c ON r.id=c.fk_tourism_model_id " +
                    "INNER JOIN " +
                    "(SELECT MIN(w.server_date) server_date,w.fk_tourism_model_id " +
                    "FROM lv_tourism_for_sale w where w.server_date>=curdate() and w.status=0 " +
                    "GROUP BY w.fk_tourism_model_id) x ON " +
                    "x.fk_tourism_model_id=r.id AND c.server_date=x.server_date " +
                    "WHERE c.status=0 AND s.`status`=1 AND s.type="+type+" " +
                    "ORDER BY s.create_time DESC ";
        return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }
    //获取是否已存在推荐
    public List<LvSpecialRecommendation> getLvSpecialListByModelId(String modelId, Integer type) {
        String sql = "SELECT  * from  lv_special_recommendation where fk_tourism_model_id=? and type=? ";
        return this.currentSession().createNativeQuery(sql, LvSpecialRecommendation.class).setParameter(1,modelId).setParameter(2,type).getResultList();
    }
}
